CREATE   PROCEDURE [dbo].[asi_ScheduledTaskAcquire]
@lockKey uniqueidentifier = null,
@category nvarchar(50) = null
AS

DECLARE @tmp nvarchar(800)

SET @tmp = N'SELECT ScheduledTaskKey, SelectionLock INTO #pick '
SET @tmp = @tmp + N'FROM ScheduledTask '
SET @tmp = @tmp + N'WHERE IsDisabled = 0 AND (SelectionLock IS NULL OR SelectionLock = ''' + CAST(@lockKey AS nvarchar(40)) + N''' OR KeepAlive < dateadd(mi, -15, getdate())) '
IF DATALENGTH(@category) > 0
BEGIN
    SET @tmp = @tmp + N'AND Category LIKE ''' + @category + N'%'' '
END

SET @tmp = @tmp + N'UPDATE ScheduledTask SET SelectionLock = ''' + CAST(@lockKey AS nvarchar(40)) + N''', KeepAlive = getdate() '
SET @tmp = @tmp + N'FROM ScheduledTask INNER JOIN #pick ON ScheduledTask.ScheduledTaskKey = #pick.ScheduledTaskKey '
SET @tmp = @tmp + N'WHERE (ScheduledTask.SelectionLock IS NULL OR ScheduledTask.SelectionLock = #pick.SelectionLock) '

SET @tmp = @tmp + N'DROP TABLE #pick '

SET @tmp = @tmp + N'SELECT count(*) FROM ScheduledTask WHERE SelectionLock = ''' + CAST(@lockKey AS nvarchar(40)) + N''''

EXEC(@tmp)

GO